
BEGIN
	IF SCHEMA_ID('Sample') IS NULL
	BEGIN
		EXEC ('CREATE SCHEMA [Sample]')
	END

	DECLARE @SQL nvarchar(max)

	SET @SQL = ''

	select @SQL += 'DROP ' + CASE TYPE WHEN 'U' THEN 'TABLE' WHEN 'IF' THEN 'FUNCTION' WHEN 'FN' THEN 'FUNCTION' WHEN 'P' THEN 'PROCEDURE'  WHEN 'V' THEN 'VIEW' END  + ' '  + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + char(13) + char(10) from sys.objects
	where type in ('U','IF','P','FN','V')
	and schema_id = SCHEMA_ID('Sample')

	EXEC (@SQL)

	delete QIC.AspectConfig where WhenInterfaceName in ('CreateReports_','VendorReport_')
	delete QIC.TokenMap where TokenMap in ('LogStart', 'LogEnd','LogInsert','LogUpdate','LogDelete')

END
GO

CREATE TABLE Sample.Product (Id int, VendorProductId varchar(50), Name varchar(50), StockOnHand int, TotalSales int, SalesThisYear int, VendorId int)
CREATE TABLE Sample.Vendor (Id int, Name varchar(50))
CREATE TABLE Sample.VendorReport (Id int IDENTITY(1,1), ReportType varchar(50), ReportFormat varchar(50), VendorId int, Data NVARCHAR(MAX), DateCreated datetime)
GO

SET NOCOUNT ON

INSERT INTO Sample.Vendor (Id,Name)
VALUES 
(1,'SHIRT VENDOR'),
(2,'JEANS VENDOR'),
(3,'SHOE VENDOR')

INSERT INTO Sample.Product (Id, Name, VendorId, VendorProductId, StockOnHand, TotalSales, SalesThisYear)
VALUES 
(1, 'MALE SHIRT', 1, '101', 10, 20, 5),
(2, 'FEMALE SHIRT', 1, '102', 0, 40, 18),
(3, 'JEANS', 2, '5000', 15, 60, 15),
(4, 'SHOE', 3, '100', 15, 60, 15)

GO

-- *******
-- NO QIC
-- *******
GO
CREATE FUNCTION Sample.GetSalesReportData(@VendorId int)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	RETURN CAST((select VendorProductId, Name, TotalSales, SalesThisYear from Product where VendorId = @VendorId FOR XML AUTO) as NVARCHAR(MAX))
END
GO

CREATE FUNCTION Sample.GetStockReportData(@VendorId int)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	RETURN CAST((select VendorProductId, Name, StockOnHand from Product where VendorId = @VendorId FOR XML AUTO) as NVARCHAR(MAX))
END
GO

CREATE PROCEDURE Sample.CreateVendorReports @VendorId int
AS
BEGIN

	SET NOCOUNT ON

	DECLARE @DATA NVARCHAR(MAX)

	SELECT @DATA = Sample.GetSalesReportData(@VendorId)
	INSERT INTO Sample.VendorReport (ReportType,ReportFormat,VendorId,Data,DateCreated) VALUES ('SALES', 'XML', @VendorId, @DATA, GETDATE())

	SELECT @DATA = Sample.GetStockReportData(@VendorId)
	INSERT INTO Sample.VendorReport (ReportType,ReportFormat,VendorId,Data,DateCreated) VALUES ('STOCK', 'XML', @VendorId, @DATA, GETDATE())
	
END

GO

truncate table Sample.VendorReport
exec Sample.CreateVendorReports 1
exec Sample.CreateVendorReports 2
exec Sample.CreateVendorReports 3
select * from Sample.VendorReport

GO

-- *******
-- WITH QIC
-- *******
-- INTERFACES, DELEGATES
GO

CREATE FUNCTION Sample.GetVendorReportData_(@DelegateName varchar(50), @VendorId int)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	RETURN NULL
END
GO

CREATE FUNCTION Sample.GetVendorReportData_SALES(@DelegateName varchar(50), @VendorId int)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	RETURN (select VendorProductId, Name, TotalSales, SalesThisYear from Product where VendorId = @VendorId FOR XML AUTO)
END
GO

CREATE FUNCTION Sample.GetVendorReportData_STOCK(@DelegateName varchar(50), @VendorId int)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	RETURN (select VendorProductId, Name, StockOnHand from Product where VendorId = @VendorId FOR XML AUTO)
END
GO

ALTER PROCEDURE Sample.CreateVendorReports @VendorId int
AS
BEGIN

	SET NOCOUNT ON

	DECLARE @DATA NVARCHAR(MAX)
	DECLARE @ReportType VARCHAR(50)

	DECLARE @ReportTypes TABLE (ReportType varchar(50))
	INSERT INTO @ReportTypes (ReportType) VALUES ('SALES'),('STOCK')

	DECLARE reportCursor CURSOR LOCAL FAST_FORWARD FOR
	SELECT ReportTypes.ReportType FROM @ReportTypes ReportTypes

	OPEN reportCursor

	WHILE (1=1)
	BEGIN
		FETCH NEXT FROM reportCursor INTO @ReportType
		IF @@FETCH_STATUS <> 0 BREAK

		SELECT @DATA = Sample.GetVendorReportData_(@ReportType,@VendorId)
		INSERT INTO Sample.VendorReport (ReportType,ReportFormat,VendorId,Data,DateCreated) VALUES (@ReportType, 'XML', @VendorId, @DATA, GETDATE())

	END

	CLOSE reportCursor
	DEALLOCATE reportCursor
	
END

GO

exec QIC.Compile
exec QIC.ScriptInterface 'GetVendorReportData_'
GO

truncate table Sample.VendorReport
exec Sample.CreateVendorReports 1
exec Sample.CreateVendorReports 2
exec Sample.CreateVendorReports 3
select * from Sample.VendorReport

GO

-- *******
-- DIFFERENT FORMATS
-- *******
GO

ALTER TABLE Sample.Vendor ADD ReportFormat varchar(50) NULL
GO

UPDATE Sample.Vendor SET ReportFormat = CASE id WHEN 1 THEN 'FLAT' ELSE 'XML' END
GO

CREATE FUNCTION Sample.GetVendorReportData_SALESXML(@DelegateName varchar(50), @VendorId int)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	RETURN (select VendorProductId, Name, TotalSales, SalesThisYear from Product where VendorId = @VendorId FOR XML AUTO)
END
GO

CREATE FUNCTION Sample.GetVendorReportData_STOCKXML(@DelegateName varchar(50), @VendorId int)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	RETURN (select VendorProductId, Name, StockOnHand from Product where VendorId = @VendorId FOR XML AUTO)
END
GO

CREATE FUNCTION Sample.GetVendorReportData_SALESFLAT(@DelegateName varchar(50), @VendorId int)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	DECLARE @DATA NVARCHAR(MAX)

	SET @DATA = 'VendorProductId,Name,TotalSales,SalesThisYear' + CHAR(13)

	SELECT @DATA +=  ISNULL(VendorProductId,'') + ',' + ISNULL(Name,'') + ',' + ISNULL(CAST(TotalSales AS VARCHAR(50)),'0') + ',' + ISNULL(CAST(SalesThisYear AS VARCHAR(50)),'0') from Product where VendorId = @VendorId

	RETURN @DATA
END
GO

CREATE FUNCTION Sample.GetVendorReportData_STOCKFLAT(@DelegateName varchar(50), @VendorId int)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	DECLARE @DATA NVARCHAR(MAX)

	SET @DATA = 'VendorProductId,Name,StockOnHand' + CHAR(13)

	SELECT @DATA +=  ISNULL(VendorProductId,'') + ',' + ISNULL(Name,'') + ',' + ISNULL(CAST(StockOnHand AS VARCHAR(50)),'0') from Product where VendorId = @VendorId

	RETURN @DATA
END
GO

ALTER PROCEDURE Sample.CreateVendorReports @VendorId int
AS
BEGIN

	SET NOCOUNT ON

	DECLARE @Data NVARCHAR(MAX)
	DECLARE @ReportType VARCHAR(50)
	DECLARE @ReportFormat VARCHAR(50)
	DECLARE @ReportDelegate VARCHAR(50)

	DECLARE @ReportTypes TABLE (ReportType varchar(50))
	INSERT INTO @ReportTypes (ReportType) VALUES ('SALES'),('STOCK')

	DECLARE reportCursor CURSOR LOCAL FAST_FORWARD FOR
	SELECT ReportTypes.ReportType, Vendor.ReportFormat, ReportTypes.ReportType + Vendor.ReportFormat FROM @ReportTypes ReportTypes
	INNER JOIN Sample.Vendor Vendor
		ON Vendor.Id = @VendorId

	OPEN reportCursor

	WHILE (1=1)
	BEGIN
		FETCH NEXT FROM reportCursor INTO @ReportType, @ReportFormat, @ReportDelegate
		IF @@FETCH_STATUS <> 0 BREAK

		SELECT @Data = Sample.GetVendorReportData_(@ReportDelegate,@VendorId)

		INSERT INTO Sample.VendorReport (ReportType,ReportFormat,VendorId,Data,DateCreated) VALUES (@ReportType, @ReportFormat, @VendorId, @Data, GETDATE())

	END

	CLOSE reportCursor
	DEALLOCATE reportCursor
	
END

GO

exec QIC.Compile
GO

truncate table Sample.VendorReport
exec Sample.CreateVendorReports 1
exec Sample.CreateVendorReports 2
exec Sample.CreateVendorReports 3
select * from Sample.VendorReport

GO

-- *******
-- LOGGING
-- *******
-- ASPECTS, DEFAULT DELEGATE, TOKENMAP, SPECIAL TOKENS
GO

CREATE TABLE Sample.Log (Id int IDENTITY(1,1), LogType VARCHAR(50), InterfaceName VARCHAR(50), InterfaceContext XML, LogDate datetime, Duration int)
GO

CREATE PROCEDURE Sample.AddLog_ (@LogType VARCHAR(50), @InterfaceName VARCHAR(50), @InterfaceContext XML, @LogId int OUT)
AS
BEGIN
	RETURN NULL
END
GO

CREATE PROCEDURE Sample.AddLog_DEFAULT (@LogType VARCHAR(50), @InterfaceName VARCHAR(50), @InterfaceContext XML, @LogId int OUT)
AS
BEGIN

	DECLARE @Duration int
	DECLARE @Now datetime

	SET @Now = GETDATE()

	IF @LogId IS NOT NULL
	BEGIN
		SELECT @Duration = DATEDIFF(MILLISECOND, LogDate, @Now) FROM Sample.Log with (nolock) where id = @LogId
	END

	INSERT INTO Sample.Log (LogType,InterfaceName,InterfaceContext,Duration,LogDate) VALUES (@LogType,@InterfaceName,@InterfaceContext,@Duration, @Now)

	SET @LogId = SCOPE_IDENTITY()
END
GO

DROP PROCEDURE Sample.CreateVendorReports
GO

CREATE PROCEDURE Sample.CreateVendorReports_ @VendorId int
AS
BEGIN
	RETURN NULL
END
GO

CREATE PROCEDURE Sample.CreateVendorReports_DEFAULT @VendorId int
AS
BEGIN

	SET NOCOUNT ON

	DECLARE @Data NVARCHAR(MAX)
	DECLARE @ReportType VARCHAR(50)
	DECLARE @ReportFormat VARCHAR(50)
	DECLARE @ReportDelegate VARCHAR(50)

	DECLARE @ReportTypes TABLE (ReportType varchar(50))
	INSERT INTO @ReportTypes (ReportType) VALUES ('SALES'),('STOCK')

	DECLARE reportCursor CURSOR LOCAL FAST_FORWARD FOR
	SELECT ReportTypes.ReportType, Vendor.ReportFormat, ReportTypes.ReportType + Vendor.ReportFormat FROM @ReportTypes ReportTypes
	INNER JOIN Sample.Vendor Vendor
		ON Vendor.Id = @VendorId

	OPEN reportCursor

	WHILE (1=1)
	BEGIN
		FETCH NEXT FROM reportCursor INTO @ReportType, @ReportFormat, @ReportDelegate
		IF @@FETCH_STATUS <> 0 BREAK

		SELECT @Data = Sample.GetVendorReportData_(@ReportDelegate,@VendorId)

		INSERT INTO Sample.VendorReport (ReportType,ReportFormat,VendorId,Data,DateCreated) VALUES (@ReportType, @ReportFormat, @VendorId, @Data, GETDATE())

	END

	CLOSE reportCursor
	DEALLOCATE reportCursor
	
END

GO

INSERT INTO QIC.AspectConfig (WhenInterfaceName, WhenDelegateName, WhenActionName, ThenInterfaceName, ThenTokenMap, ThenReturnToken) VALUES 
('CreateVendorReports_','DEFAULT', 'BEFORE','AddLog_','LogStart','@LogStatus'),
('CreateVendorReports_','DEFAULT', 'AFTER','AddLog_','LogEnd','@LogStatus')

INSERT INTO QIC.TokenMap (TokenMap,WhenToken,ThenToken)
VALUES ('LogStart','@LogType','''START'''),
('LogEnd','@LogType','''END''')


exec QIC.Compile
exec QIC.ScriptInterface 'AddLog_'
exec QIC.ScriptInterface 'CreateVendorReports_'

truncate table Sample.VendorReport
truncate table Sample.Log
exec Sample.CreateVendorReports_ 1
exec Sample.CreateVendorReports_ 2
exec Sample.CreateVendorReports_ 3
select * from Sample.VendorReport
select * from Sample.Log

GO

GO
-- *******
-- MOCK
-- *******
-- CONFIGURATION CONVENTIONS
GO

CREATE PROCEDURE Sample.SaveVendorReport_ @VendorId int, @ReportType VARCHAR(50), @ReportFormat VARCHAR(50), @Data NVARCHAR(MAX)
AS
BEGIN

	RETURN NULL
	
END

GO

CREATE PROCEDURE Sample.SaveVendorReport_DEFAULT @VendorId int, @ReportType VARCHAR(50), @ReportFormat VARCHAR(50), @Data NVARCHAR(MAX)
AS
BEGIN

	SET NOCOUNT ON

	INSERT INTO Sample.VendorReport (ReportType,ReportFormat,VendorId,Data,DateCreated) VALUES (@ReportType, @ReportFormat, @VendorId, @Data, GETDATE())
	
END

GO

CREATE PROCEDURE Sample.SaveVendorReport_DEFAULT_TEST @VendorId int, @ReportType VARCHAR(50), @ReportFormat VARCHAR(50), @Data NVARCHAR(MAX)
AS
BEGIN

	SET NOCOUNT ON
	PRINT @DATA
END

GO

ALTER PROCEDURE Sample.CreateVendorReports_DEFAULT @VendorId int
AS
BEGIN

	SET NOCOUNT ON

	DECLARE @Data NVARCHAR(MAX)
	DECLARE @ReportType VARCHAR(50)
	DECLARE @ReportFormat VARCHAR(50)
	DECLARE @ReportDelegate VARCHAR(50)

	DECLARE @ReportTypes TABLE (ReportType varchar(50))
	INSERT INTO @ReportTypes (ReportType) VALUES ('SALES'),('STOCK')

	DECLARE reportCursor CURSOR LOCAL FAST_FORWARD FOR
	SELECT ReportTypes.ReportType, Vendor.ReportFormat, ReportTypes.ReportType + Vendor.ReportFormat FROM @ReportTypes ReportTypes
	INNER JOIN Sample.Vendor Vendor
		ON Vendor.Id = @VendorId

	OPEN reportCursor

	WHILE (1=1)
	BEGIN
		FETCH NEXT FROM reportCursor INTO @ReportType, @ReportFormat, @ReportDelegate
		IF @@FETCH_STATUS <> 0 BREAK

		SELECT @Data = Sample.GetVendorReportData_(@ReportDelegate,@VendorId)

		EXEC Sample.SaveVendorReport_ @VendorId, @ReportType, @ReportFormat, @Data

	END

	CLOSE reportCursor
	DEALLOCATE reportCursor
	
END

GO

exec QIC.Compile 'TEST'
GO

truncate table Sample.VendorReport
exec Sample.CreateVendorReports_ 1
exec Sample.CreateVendorReports_ 2
exec Sample.CreateVendorReports_ 3
select * from Sample.VendorReport

GO

exec QIC.Compile
GO

truncate table Sample.VendorReport
exec Sample.CreateVendorReports_ 1
exec Sample.CreateVendorReports_ 2
exec Sample.CreateVendorReports_ 3
select * from Sample.VendorReport

GO


GO
-- *******
-- VIEWS
-- *******
GO

CREATE TABLE Sample.VendorSalesReport (Id int IDENTITY(1,1), ReportFormat varchar(50), VendorId int, Data NVARCHAR(MAX), DateCreated datetime)
CREATE TABLE Sample.VendorStockReport (Id int IDENTITY(1,1), ReportFormat varchar(50), VendorId int, Data NVARCHAR(MAX), DateCreated datetime)
GO

CREATE VIEW Sample.VendorReport_
AS
	SELECT CAST(NULL as varchar(50)) DelegateName, CAST(NULL as int) Id, CAST(NULL as varchar(50)) ReportFormat, CAST(NULL as int) VendorId, CAST(NULL as nvarchar(max)) Data, CAST(NULL as datetime) DateCreated WHERE 1=0 
GO

CREATE VIEW Sample.VendorReport_SALES
AS
	SELECT Id, ReportFormat, VendorId, Data, DateCreated 
	FROM Sample.VendorSalesReport
GO

CREATE VIEW Sample.VendorReport_STOCK
AS
	SELECT Id, ReportFormat, VendorId, Data, DateCreated 
	FROM Sample.VendorStockReport
GO

ALTER PROCEDURE Sample.SaveVendorReport_DEFAULT @VendorId int, @ReportType VARCHAR(50), @ReportFormat VARCHAR(50), @Data NVARCHAR(MAX)
AS
BEGIN

	SET NOCOUNT ON

	INSERT INTO Sample.VendorReport_ (DelegateName,ReportFormat,VendorId,Data,DateCreated) VALUES (@ReportType, @ReportFormat, @VendorId, @Data, GETDATE())
	
END

GO

INSERT INTO QIC.AspectConfig (WhenInterfaceName, WhenDelegateName, WhenActionName, ThenInterfaceName, ThenTokenMap, ThenReturnToken) VALUES 
('VendorReport_','', 'BEFORE INSERT','AddLog_','LogInsert','@LogStatus'),
('VendorReport_','', 'BEFORE UPDATE','AddLog_','LogUpdate','@LogStatus'),
('VendorReport_','', 'BEFORE DELETE','AddLog_','LogDelete','@LogStatus')

INSERT INTO QIC.TokenMap (TokenMap,WhenToken,ThenToken)
VALUES ('LogInsert','@LogType','''INSERT'''),
('LogUpdate','@LogType','''UPDATE'''),
('LogDelete','@LogType','''DELETE''')
GO

exec QIC.Compile
exec QIC.ScriptInterface 'VendorReport_'
GO

truncate table Sample.Log
truncate table Sample.VendorReport
exec Sample.CreateVendorReports_ 1
exec Sample.CreateVendorReports_ 2
exec Sample.CreateVendorReports_ 3
select * from Sample.VendorStockReport
select * from Sample.VendorSalesReport
select * from Sample.VendorReport_
select * from Sample.Log

GO

truncate table Sample.Log
UPDATE Sample.VendorReport_ SET Data = 'UPDATED DATA' WHERE ReportFormat = 'FLAT'
DELETE Sample.VendorReport_ WHERE DelegateName = 'STOCK' AND VendorId = 2
select * from Sample.VendorReport_
select * from Sample.Log

GO

